package grapid.database;

import java.util.ArrayList;
import java.util.List;

import grapid.Grapid;

/**
 * SQL语句管理类<br />
 * 主要用于Table相关操作 (未完成)
 * 
 * @author grapid
 * @version 2.0
 * @deprecated
 * 
 */
public class GQueryT {

	/**
	 * 数据库类型
	 */
	private Integer dbtype = Grapid.DEFAULT_DBTYPE;
	/**
	 * 工作模式
	 */
	private int mode = Grapid.DEFAULT_GQUERY_MODE;

	/**
	 * 数据库名
	 */
	private String dbName;
	/**
	 * 表名
	 */
	private String tableName;

	/**
	 * 缓存数据
	 */
	private List<ColumnBean> columnList = new ArrayList<ColumnBean>();

	/**
	 * 构造函数
	 */
	public GQueryT() {
	}

	/**
	 * 构造函数
	 * 
	 * @param tableName
	 *            表名
	 */
	public GQueryT(String tableName) {
		this.tableName = tableName;
	}

	/**
	 * 构造函数
	 * 
	 * @param tableName
	 *            表名
	 * @param dbtype
	 *            数据库类型
	 * @param mode
	 *            CQuery工作模式
	 */
	public GQueryT(String tableName, Integer dbtype, int mode) {
		this.tableName = tableName;
		this.dbtype = dbtype;
		this.mode = mode;
	}

	/**
	 * 添加列
	 * 
	 * @param columnName
	 *            列名
	 * @param type
	 *            类型
	 * @param length
	 *            长度1
	 * @param length2
	 *            长度2
	 * @param notNull
	 *            是否可为空
	 * @param primaryKey
	 *            是否主键
	 * @param identity
	 *            是否自增长
	 * @param identityStart
	 *            自增长起始值
	 * @param identityStep
	 *            自增长步长
	 * @param columnDefault
	 *            默认值
	 */
	public void addColumn(String columnName, String type, Object length, Integer length2, boolean notNull,
			boolean primaryKey, boolean identity, int identityStart, int identityStep, Object columnDefault) {
		ColumnBean bean = new ColumnBean();
		bean.setColumnName(columnName);
		bean.setType(type);
		bean.setLength(length);
		bean.setLength2(length2);
		bean.setNotNull(notNull);
		bean.setPrimaryKey(primaryKey);
		bean.setIdentity(identity);
		bean.setIdentityStart(identityStart);
		bean.setIdentityStep(identityStep);
		bean.setColumnDefault(columnDefault);
		this.columnList.add(bean);
	}

	/**
	 * 添加列
	 * 
	 * @param columnName
	 *            列名
	 * @param type
	 *            类型
	 * @param length
	 *            长度1
	 */
	public void addColumn(String columnName, String type, Object length) {
		ColumnBean bean = new ColumnBean();
		bean.setColumnName(columnName);
		bean.setType(type);
		bean.setLength(length);
		this.columnList.add(bean);
	}

	/**
	 * 添加列
	 * 
	 * @param columnName
	 *            列名
	 * @param type
	 *            类型
	 * @param length
	 *            长度1
	 * @param notNull
	 *            是否可为空
	 * @param columnDefault
	 *            默认值
	 */
	public void addColumn(String columnName, String type, Object length, boolean notNull, Object columnDefault) {
		ColumnBean bean = new ColumnBean();
		bean.setColumnName(columnName);
		bean.setType(type);
		bean.setLength(length);
		bean.setNotNull(notNull);
		bean.setColumnDefault(columnDefault);
		this.columnList.add(bean);
	}

	/**
	 * 取SQL语句 - 查询表是否存在
	 * 
	 * @return 返回检查表是否存在sql语句
	 */
	public String getSQLExists() {
		String sql = "";
		if (Grapid.DB_MYSQL.equals(this.dbtype))
			// sql = "SHOW TABLES LIKE '" + this.tableName + "'";
			sql = "SELECT COUNT(0) FROM information_schema.tables WHERE table_name='" + this.tableName + "'"
					+ (this.dbName == null ? "" : " AND table_schema='" + this.dbName + "'");
		else if (Grapid.DB_MSSQL.equals(this.dbtype))
			sql = "SELECT COUNT(0) FROM " + (this.dbName == null ? "" : this.dbName + ".")
					+ "dbo.SYSOBJECTS WHERE name='" + this.tableName + "'";
		else if (Grapid.DB_SQLITE.equals(this.dbtype))
			sql = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='" + this.tableName + "'";
		debug(sql);
		return sql;
	}

	/**
	 * 取SQL语句 - 创建数据表
	 * 
	 * @return 返回创建表sql语句
	 */
	public String getSQLCreate() {
		String sql = "";
		if (this.columnList.size() > 0) {
			if (Grapid.DB_MYSQL.equals(this.dbtype)) {
			} else if (Grapid.DB_MSSQL.equals(this.dbtype)) {
				String primaryKey = null;
				for (ColumnBean bean : this.columnList) {
					if (bean.getColumnName() != null && bean.getType() != null) {
						sql += ", [" + bean.getColumnName() + "] ";
						if (bean.isIdentity()) {
							sql += "[int] IDENTITY(" + bean.getIdentityStart() + "," + bean.getIdentityStep() + ") ";
						} else {
							String type = bean.getType().toLowerCase();
							sql += "[" + type + "]";
							if ("char".equals(type) || "varchar".equals(type) || "nchar".equals(type)
									|| "nvarchar".equals(type) || "binary".equals(type) || "varbinary".equals(type)) {
								sql += "(" + bean.length + ")";
							} else if ("decimal".equals(type) || "numeric".equals(type)) {
								sql += "(" + bean.length + "," + bean.length2 + ")";
							}
							sql += (bean.isNotNull() ? " NOT NULL" : " NULL");
							if (bean.getColumnDefault() != null) {
								if (bean.getColumnDefault() instanceof Integer) {
									sql += " DEFAULT ((" + bean.getColumnDefault() + "))";
								} else {
									sql += " DEFAULT ('" + bean.getColumnDefault() + "')";
								}
							}
						}
						if (bean.isPrimaryKey())
							primaryKey = bean.getColumnName();
					}
				}
				if (!"".equals(sql)) {
					sql = "CREATE TABLE [dbo].[" + this.tableName + "](" + sql.substring(1);
					if (primaryKey != null) {
						sql += ",CONSTRAINT [PK_" + this.tableName + "] PRIMARY KEY CLUSTERED([" + primaryKey
								+ "] ASC)";
					}
					sql += ")";
				}
			} else if (Grapid.DB_SQLITE.equals(this.dbtype)) {

			}
		}
		debug(sql);
		return sql;
	}

	/**
	 * 取SQL语句 - 删除数据表
	 * 
	 * @return 返回删除数据表sql语句
	 */
	public String getSQLDrop() {
		String sql = "DROP TABLE " + this.tableName;
		debug(sql);
		return sql;
	}

	/**
	 * debug方法
	 * 
	 * @param sql
	 */
	private void debug(String sql) {
		if (!"".equals(sql) && (this.mode & Grapid.GQuery_MODE_DEBUG) != 0) {
			System.out.println(sql);
		}
	}

	/**
	 * 列属性类
	 * 
	 * @author grapid
	 * 
	 */
	private class ColumnBean {
		private String columnName; // 列名
		private String type; // 类型
		private Object length; // 类型长度
		private Integer length2; // 类型长度2
		private boolean notNull = true; // 是否允许为空
		private Object columnDefault; // 默认值
		private boolean primaryKey = false; // 主键
		private boolean identity; // 是否自增长
		private int identityStart = 1; // 自增长开始值
		private int identityStep = 1; // 自增长增量

		public String getColumnName() {
			return columnName;
		}

		public void setColumnName(String columnName) {
			this.columnName = columnName;
		}

		public String getType() {
			return type;
		}

		public void setType(String type) {
			this.type = type;
		}

		public Object getLength() {
			return length;
		}

		public void setLength(Object length) {
			this.length = length;
		}

		public Object getColumnDefault() {
			return columnDefault;
		}

		public void setColumnDefault(Object columnDefault) {
			this.columnDefault = columnDefault;
		}

		public boolean isIdentity() {
			return identity;
		}

		public void setIdentity(boolean identity) {
			this.identity = identity;
		}

		public int getIdentityStart() {
			return identityStart;
		}

		public void setIdentityStart(int identityStart) {
			this.identityStart = identityStart;
		}

		public int getIdentityStep() {
			return identityStep;
		}

		public void setIdentityStep(int identityStep) {
			this.identityStep = identityStep;
		}

		public boolean isPrimaryKey() {
			return primaryKey;
		}

		public void setPrimaryKey(boolean primaryKey) {
			this.primaryKey = primaryKey;
		}

		public Integer getLength2() {
			return length2;
		}

		public void setLength2(Integer length2) {
			this.length2 = length2;
		}

		public boolean isNotNull() {
			return notNull;
		}

		public void setNotNull(boolean notNull) {
			this.notNull = notNull;
		}
	}

	/**
	 * 获取数据库类型
	 * 
	 * @return 返回数据库类型
	 */
	public Integer getDbtype() {
		return dbtype;
	}

	/**
	 * 设置数据库类型
	 * 
	 * @param dbtype
	 *            数据库类型
	 */
	public void setDbtype(Integer dbtype) {
		this.dbtype = dbtype;
	}

	/**
	 * 获取CQuery工作模式
	 * 
	 * @return 返回CQuery工作模式
	 */
	public int getMode() {
		return mode;
	}

	/**
	 * 设置CQuery工作模式
	 * 
	 * @param mode
	 *            CQuery工作模式
	 */
	public void setMode(int mode) {
		this.mode = mode;
	}

	/**
	 * 获取CQueryT操作数据库名
	 * 
	 * @return 返回CQueryT操作数据库名
	 */
	public String getDbName() {
		return dbName;
	}

	/**
	 * 设置CQueryT操作数据库名
	 * 
	 * @param dbName
	 *            CQueryT操作数据库名
	 */
	public void setDbName(String dbName) {
		this.dbName = dbName;
	}

	/**
	 * 获取CQuery操作表名
	 * 
	 * @return 返回CQuery操作表名
	 */
	public String getTableName() {
		return tableName;
	}

	/**
	 * 设置CQuery操作表名
	 * 
	 * @param tableName
	 *            CQuery操作表名
	 */
	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	/**
	 * 获取更新列信息
	 * 
	 * @return 返回更新列信息
	 */
	public List<ColumnBean> getColumnList() {
		return columnList;
	}

	/**
	 * 设置更新列信息
	 * 
	 * @param columnList
	 *            更新列信息
	 */
	public void setColumnList(List<ColumnBean> columnList) {
		this.columnList = columnList;
	}
}
